<?php
/*
 *  This file is part of Urd.
 *
 *  Urd is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 3 of the License, or
 *  (at your option) any later version.
 *  Urd is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program. See the file "COPYING". If it does not
 *  exist, see <http://www.gnu.org/licenses/>.
 *
 * $LastChangedDate: 2008-07-15 15:42:06 +0200 (Tue, 15 Jul 2008) $
 * $Rev: 1296 $
 * $Author: gavinspearhead $
 * $Id: browse.php 1296 2008-07-15 13:42:06Z gavinspearhead $
 */
if (!defined('ORIGINAL_PAGE')) define('ORIGINAL_PAGE', $_SERVER['PHP_SELF']);

$pathidx = realpath(dirname(__FILE__));

require_once "$pathidx/../functions/html_includes.php";

if (!isset($_SESSION['setdata']) || !is_array($_SESSION['setdata'])) 
	$_SESSION['setdata'] = array();
$rprefs = load_config($db);

$show_advanced_search = FALSE;
$message = '';

$sort_orders = array ('', 'complete', 'subject', 'date', 'size', 'better_subject', 'rating');
$groupID = $origroupID = get_request('groupID', 0);

// get last update times for groups
$sql = "SELECT \"groupid\", \"last_update_seen\" FROM usergroupinfo WHERE \"userid\" = '$userID'";
if ($groupID != 0 && $groupID != '')
	$sql .= " AND \"groupid\" = '$groupID'";
$res = $db->execute_query($sql);
$group_lastupdate = array();
if ($res !== FALSE) {
	foreach ($res as $row) 
		$group_lastupdate["{$row['groupid']}"] = $row['last_update_seen'];
}

// Process actions:
if ($_SERVER['REQUEST_METHOD'] == 'POST') 
	$message = process_which_button($db, $userID, USERSETTYPE_GROUP, $groupID, $__message);

// Create title:

$totbin = get_total_ng_sets($db);
$title = $LN['urdname'] . ' - '. $LN['browse_download'] . ' ' . $LN['from'] . ' ' . $totbin. ' ' . $LN['sets'] . '!'; 

// Get the required values:
// ori = passed back to the front-end, these are used in the database (backend):
$search = html_entity_decode(trim(get_request('search', '')));
if (utf8_decode($search) == (html_entity_decode("<{$LN['search']}>")))
    $search = '';
$orisearch  = utf8_decode($search);
$offset  = $orioffset  = get_request('offset', 0);
$order   = $oriorder   = get_request('order', '');
$flag    = $oriflag    = get_request('flag', '');
$maxage  = $orimaxage  = get_request('maxage', '');
$minage  = $oriminage  = get_request('minage', '');
$ominsetsize = get_request('minsetsize', NULL);
$omaxsetsize = get_request('maxsetsize', NULL);
$maxrating  = $orimaxrating  = get_request('maxrating', '');
$minrating  = $oriminrating  = get_request('minrating', '');
$maxcomplete  = $orimaxcomplete  = get_request('maxcomplete', '');
$mincomplete  = $orimincomplete  = get_request('mincomplete', '');

$setid	 = get_request('setid', '');

if ($order == 'subject') 
	$order = 'better_subject';

$db->escape($groupID);
$db->escape($search);
$db->escape($offset);
$db->escape($maxage);
$db->escape($minage);
$db->escape($minrating);
$db->escape($maxrating);
$db->escape($mincomplete);
$db->escape($maxcomplete);
$db->escape($order);
$db->escape($flag);
$db->escape($setid);

$Qgroups = '';
if ($groupID != 0 && $groupID != '')
	$Qgroups .= " OR \"groupid\" = '$groupID'";

// Get the newsgroups:
$res = $db->execute_query("SELECT groups.\"ID\", groups.\"name\", groups.\"postcount\" FROM groups LEFT JOIN usergroupinfo ON groups.\"ID\" = \"groupid\" " . 
        " WHERE \"active\" = '" . NG_SUBSCRIBED . "' AND (\"visible\" > 0 OR \"visible\" IS NULL $Qgroups) ORDER BY \"name\"");

if (!$res) 
    $res = array();

$subscribedgroups = array();
$c = 0;
$next = $prev = '';
foreach ($res as $arr) {
	list($size, $suffix) = format_size($arr['postcount'], 'h', '', 1000);
	if ($size == 0 && ($arr['ID'] != $groupID)) // don't show empty groups anyway
		continue;
	$subscribedgroups[] = array(
		'id'=>$arr['ID'], 
		'name'=>$arr['name'], 
		'shortname'=>shorten_newsgroup_name($arr['name']),
		'article_count'=>"$size$suffix"
    );
    if ($arr['ID'] == $groupID) {
        $next = $c+1;
        $prev = $c-1;
    }
    $c++;
}

if (isset($subscribedgroups[$next]['id']))
    $next_group = $subscribedgroups[$next]['id'];
else 
    $next_group = '';

if (isset($subscribedgroups[$prev]['id']))
    $prev_group = $subscribedgroups[$prev]['id'];
else 
    $prev_group = '';

// Build the page skipper:

// Determine maximum sets to be displayed, and display the current page:
$Qgroup = $Qsearch = $Qage = $Qflag = $Qsetid = $Qkill = $Qnewsize = $Qnewgroup1 = $Qnewgroup2 = $Qrating = '';

$search_type = $db->get_pattern_search_command('LIKE'); // get the operator we need for the DB LIKE for mysql or ~~* for postgres

if ($groupID != 0 && $groupID != '') {
	// Display this group only
	$Qgroup = " AND setdata.\"groupID\" = $groupID ";
    $Qnewgroup1 = "usergroupinfo.\"groupid\" = setdata.\"groupID\"";
} else {
	$Qgroup = ' ';
	// Display all groups, except the ones that this user has marked invisible
	$Qnewgroup1 = "usergroupinfo.\"groupid\" = setdata.\"groupID\" ";
	$Qnewgroup2 = " AND (usergroupinfo.\"visible\" = 1 OR usergroupinfo.\"visible\" IS NULL) ";
}

// Search google style:
if ($search != '') {
    $search = trim(str_replace('*', ' ', $search));
	$keywords = explode(' ', $search);
	$Qsearch1 = $Qsearch2 = $Qsearch3 = '';
	foreach ($keywords as $keyword) {
        if ($keyword[0] == '-') {
            $not = 'NOT';
            $keyword = ltrim($keyword, '-');
        } else 
            $not = '';

		if ($keyword != '') {
			$Qsearch1 .= " $not \"subject\" $search_type '%{$keyword}%' AND "; // nasty: like is case sensitive in psql, insensitive in mysql
			$Qsearch2 .= " $not extsetdata2.\"value\" $search_type '%{$keyword}%' AND ";
		}
	}
	$Qsearch .=  "AND ( ($Qsearch1 1=1) OR ( $Qsearch2 1=1) )";
}

if (is_numeric($maxage) && $maxage > 0) {
	$Qage .= 'AND ('. time() . " - date) / 3600 / 24 <= $maxage ";
	$show_advanced_search = TRUE;
}else 
	$maxage = '';
if (is_numeric($minage) && $minage > 0) {
	$show_advanced_search = TRUE;
	$Qage .= 'AND ('. time() . " - date) / 3600 / 24 >= $minage ";
} else 
	$minage = '';

if (isset($prefs['maxsetsize'])) {
	$maxsetsize = $prefs['maxsetsize'];
} else {
	$maxsetsize = 0;
}

if (isset($prefs['minsetsize'])) {
	$minsetsize = $prefs['minsetsize'];
} else {
	$minsetsize = 0;
}

if($setid != '') 
	$Qsetid = " AND \"setID\"='$setid' ";

if (is_numeric($ominsetsize))  {
	$show_advanced_search = TRUE;
	$minsetsize = $ominsetsize * 1024 * 1024;
	$db->escape($minsetsize, TRUE);
	$Qnewsize = " AND ( setdata.\"size\" >= $minsetsize ) ";
} else if ($Qsearch == '') {
	$Qnewsize = " AND ( setdata.\"size\" >= ((SELECT CASE WHEN \"minsetsize\" IS NULL THEN $minsetsize ELSE minsetsize END) * 1024 * 1024))";
}

if (is_numeric($omaxsetsize))  {
	$maxsetsize = $omaxsetsize * 1024 * 1024;
	$show_advanced_search = TRUE;
	$db->escape($maxsetsize, TRUE);
	$Qnewsize .= " AND ( setdata.size <= $maxsetsize ) ";
} else if ($Qsearch == '') {
	if  ($maxsetsize == 0)
		$Qnewsize .= " AND (usergroupinfo.\"maxsetsize\" = 0 OR maxsetsize IS NULL OR setdata.size <= ( maxsetsize * 1024 * 1024)) ";
	else 
		$Qnewsize .= " AND (usergroupinfo.\"maxsetsize\" = 0 OR (setdata.size <= ((SELECT CASE WHEN maxsetsize IS NULL THEN $maxsetsize ELSE maxsetsize END) * 1024 * 1024 ))) ";
}

$rss_flag = '';

$killflag = FALSE;
if ($flag == 'read') {
	$Qflag = ' AND usersetinfo."statusread"=1 ';
	$Qkill = ' AND (usersetinfo."statuskill" != 1 OR usersetinfo."statuskill" IS NULL)';
} else if ($flag == 'kill') {
	$killflag = TRUE;
	$Qkill = ' AND usersetinfo."statuskill"=1 ';
} else if ($flag == 'interesting') {
	$Qkill = ' AND (usersetinfo."statuskill" != 1 OR usersetinfo."statuskill" IS NULL)';
	$Qflag = ' AND usersetinfo."statusint"=1 ';
	$rss_flag = "&amp;flag=interesting&amp;userid=$userID";
} else if ($flag == 'nzb') {
	$Qflag = ' AND usersetinfo."statusnzb"=1 ';
	$Qkill = ' AND (usersetinfo."statuskill" != 1 OR usersetinfo."statuskill" IS NULL)';
} else {
	$Qkill = ' AND (usersetinfo."statuskill" != 1 OR usersetinfo."statuskill" IS NULL)';
}

// Validation of order:
$def_sort = isset($prefs['defaultsort']) ? $prefs['defaultsort'] : 'better_subject';
$orderfield = str_ireplace(' desc', '', $order); // $order should be 'complete/subject/date/size' and optional ' desc' or 'asc'.
$orderfield = str_ireplace(' asc', '', $orderfield); 

$perpage = get_maxperpage($db, $userID);

if (is_numeric ($minrating)) {
	$show_advanced_search = TRUE;
	$Qrating .= " AND (CAST(extsetdata3.\"value\" AS DECIMAL(5, 2)) >= $minrating) "; 
}
if (is_numeric ($maxrating)) {
	$show_advanced_search = TRUE;
	$Qrating .= " AND (CAST(extsetdata3.\"value\" AS DECIMAL(5, 2)) <= $maxrating) ";
}

// make the rss URL
$rss_limit = $perpage;

if ($groupID == 0) {
	$rss_groupid = '0';
	$rss_minsetsize = $minsetsize;
} else {
	$rss_minsetsize = get_minsetsize_group($db, $groupID, $userID, $minsetsize);
	$rss_groupid = $groupID;
}
$rss_maxage = $orimaxage;
$url = $rprefs['url']; 

$rssurl = $url . "html/rss.php?groupID=$rss_groupid&amp;limit=$rss_limit&amp;minsize=$rss_minsetsize&amp;maxage=$rss_maxage$rss_flag";

if (!in_array(strtolower($orderfield), $sort_orders))
	$order = $def_sort;
if (!empty($order)) {
	if ($order == 'rating') 
		$order = 'extsetdata3.value IS NULL, CAST(extsetdata3."value" AS decimal(5, 2))';
	$Qorder = $order;
} else 
	$Qorder = $def_sort;

if($setid != '') {
	// when searching for a set ID (from RSS) we remove all other limits
	$Qgroup = $Qsearch = $Qage = $Qflag = $Qnewsize = $Qnewcomplete = '';
	$Qsetid = " AND setdata.\"ID\" $search_type '$setid' ";
}

if (is_numeric($mincomplete) || is_numeric($maxcomplete)) {
	$show_advanced_search = TRUE;
	if (is_numeric($mincomplete))
		$Qcomplete = " AND (\"articlesmax\"=0 OR floor(\"binaries\" * 100 / GREATEST(1, \"articlesmax\")) >= $mincomplete ) ";
	if (is_numeric($maxcomplete))
		$Qcomplete = " AND (\"articlesmax\"=0 OR floor(\"binaries\" * 100 / GREATEST(1, \"articlesmax\")) <= $maxcomplete ) ";
} else if ($prefs['setcompleteness'] > 0)
	$Qcomplete = " AND (\"articlesmax\"=0 OR floor(\"binaries\" * 100 / GREATEST(1, \"articlesmax\")) >= {$prefs['setcompleteness']})";/// euah ... the horror... but it is ansi sql compliant... no refers to as fields in where clauses ...
else 
	$Qcomplete = '';

// prepend with selects and optionally append order stuff
$basic_browse_query = 
	" FROM setdata " . 
	" LEFT JOIN usergroupinfo ON ($Qnewgroup1 AND (usergroupinfo.\"userid\" = $userID)) " .
	" LEFT JOIN usersetinfo ON ((usersetinfo.\"setID\" = setdata.\"ID\") AND (usersetinfo.\"userID\" = $userID)) AND (usersetinfo.\"type\" = '" . USERSETTYPE_GROUP . "') " . 
	" LEFT JOIN extsetdata AS extsetdata2 ON (extsetdata2.\"setID\" = setdata.\"ID\" AND extsetdata2.\"name\" = 'setname' AND extsetdata2.\"type\" = '" . USERSETTYPE_GROUP . "' ) " .
	" LEFT JOIN extsetdata AS extsetdata1 ON (extsetdata1.\"setID\" = setdata.\"ID\" AND extsetdata1.\"name\" = 'link' AND extsetdata1.\"type\" = '" . USERSETTYPE_GROUP . "') " .
	" LEFT JOIN extsetdata AS extsetdata3 ON (extsetdata3.\"setID\" = setdata.\"ID\" AND extsetdata3.\"name\" = 'score' AND extsetdata3.\"type\" = '" . USERSETTYPE_GROUP . "' ) " .
	" WHERE (1=1 $Qnewgroup2 $Qsearch $Qsetid $Qgroup $Qkill $Qflag $Qnewsize $Qrating) AND ((1=1 $Qage $Qcomplete) OR usersetinfo.\"statusint\" = 1) "  ;

// Get maximum:
$sql = "COUNT(setdata.\"ID\") AS cnt " . $basic_browse_query;
//echo $sql;
$res = $db->select_query($sql);

if ($res === FALSE || !isset($res[0]['cnt'])) 
	throw new exception($LN['error_setsnumberunknown']);
$totalsets = $res[0]['cnt'];

if ($orioffset >= $totalsets){
    $orioffset = $offset = 0;
    $db->escape($offset);
}

// Build pages array:
$size = SKIPPER_SIZE;
$totalpages = ceil($totalsets / $perpage);      // Total number of pages.
$activepage = ceil(($offset+1) / $perpage);     // This is the page we're on. (+1 because 0/100 = page 1)
$start = max($activepage - floor($size/2), 1);  // We start at 1 unless we're now on page 12, then we show page 2.
$end = min($start + $size, $totalpages);        // We don't go beyond 'totalpages' ofcourse.
$start = max($end - $size, 1);                  // Re-check $start, in case the pagenumber is near the end
$pages = array();
$params = "&groupID={$groupID}&minage={$oriminage}&maxage={$orimaxage}&search={$search}&order={$order}&flag={$flag}&minsetsize={$ominsetsize}&maxsetsize={$omaxsetsize}&minrating={$oriminrating}&maxrating={$orimaxrating}&mincomplete={$orimincomplete}&maxcomplete={$orimaxcomplete}";

for ($i = 1; $i <= $totalpages; $i++) {
	$thispage = array();
	$thispage['number'] = $i;
	$pageoffset = ($i - 1) * $perpage;          // For page 1, offset = 0.
	$url = $_SERVER['PHP_SELF'] . "?offset={$pageoffset}" . $params; 
	$thispage['url'] = $url;
	// distance is the distance from the current page, maximum of 5. Used to colour close pagenumbers:
	$thispage['distance'] = min(abs($activepage - $i), 5);
	$pages[] = $thispage;
}

$now = time();
//SELECT CASE WHEN usergroupinfo.\"minsetsize\" > $minsetsize THEN usergroupinfo.\"minsetsize\" ELSE $minsetsize END)
// Newer, faster query:
$sql = " *, (100 * \"binaries\" / GREATEST(1, \"articlesmax\")) AS \"complete\", ($now - \"date\") AS \"age\", \"date\", usersetinfo.\"statusint\" AS interesting, " .
	" usersetinfo.\"statusnzb\" AS \"nzbcreated\", usersetinfo.\"statusread\" AS \"alreadyread\", extsetdata2.\"value\" AS \"bettername\", extsetdata1.\"value\" AS \"imdblink\", " .
	" (CASE WHEN extsetdata3.\"value\" IS NULL THEN '0' ELSE extsetdata3.\"value\" END) AS \"rating\", " .
        " (CASE WHEN extsetdata2.\"value\" IS NULL THEN setdata.\"subject\" ELSE  extsetdata2.\"value\" END) AS better_subject ";
$sql .=	$basic_browse_query;
$sql .= " ORDER BY (usersetinfo.\"statusint\" <> 1 OR usersetinfo.\"statusint\" IS NULL) ASC, $Qorder";
//echo "<br>";
//echo $sql ."\n";
$setres = $db->select_query($sql, $perpage, $offset);
// Get the set data
$allsets = array();
// If no sets exist, create empty array:
if (!is_array($setres)) 
	$setres = array();
$number = $offset;

foreach ($setres as $arr) {
	// Show bar around interesting when applicable:
	$thisset = array();
	$thisset['interesting'] = $arr['statusint'];
	$thisset['sid'] = $arr['ID'];
	$thisset['added'] = (is_array($_SESSION['setdata']) && in_setdata($arr['ID'], 'group', $_SESSION['setdata'])) ? 1 : 0;
	$thisset['nzb'] = $arr['nzbcreated'];
	$thisset['read'] = $arr['alreadyread'];
	$thisset['subject'] = $arr['subject']; 
	if ($arr['rating'] != 0)
		$thisset['rating'] = sprintf("%.1f", $arr['rating']);
	else 
		$thisset['rating'] = '';

	if ($arr['imdblink'] != NULL)
		$thisset['imdblink'] = $arr['imdblink'] ;
	else 
		$thisset['imdblink'] = '';

	if ($arr['bettername'] != '') 
		$thisset['name'] = utf8_decode(html_entity_decode($arr['bettername']));
	else {
		$clearname = $arr['subject'];
		// filter common spam in subjects
		$name = preg_replace('/(\byEnc\b)|(\breq:)|(\bwww\.[\w\.]*\b)|(\bhttp:\/\/[\w\.]*\b)|([=><#])/i', '', $clearname);
		$thisset['name'] = utf8_decode($name);// we utf8_encode before putting it in the db (otherwise pg will complain); but why need we decode it here so that all funny (german) characters are shown properly???
	}

	// Determine completeness:
	if ($arr['articlesmax'] == 0) 
		$complete = '-1';
	else 
		$complete = floor($arr['complete']);

	$thisset['complete'] = $complete;
	$groupID = $arr['groupID'];
	if (isset($_SESSION['last_login']) && isset ($group_lastupdate["$groupID"]) && $_SESSION['last_login'] > 0 && $group_lastupdate["$groupID"] > 0) {
		$last_check_time = min($_SESSION['last_login'], $group_lastupdate["$groupID"]);
		$thisset['new_set'] = ($arr['date'] > $last_check_time) ? 1 : 0;
	} else
		$thisset['new_set'] = 0;
	$now = time();
	$age  = ($now > $arr['date']) ? $now - $arr['date'] : 0;

	$thisset['age'] = readable_time($age, 'largest_two');
	$thisset['size'] = readable_size($arr['size'],1 , $LN['byte_short']);
	$number++;
	$thisset['number'] = $number;
	$allsets[] = $thisset;
}

init_smarty($title);

$smarty->assign('rssurl',		$rssurl);

$smarty->assign('search',		$orisearch);
$smarty->assign('isadmin',		$isadmin);
$smarty->assign('groupID',		$origroupID);
$smarty->assign('next_group',	$next_group);
$smarty->assign('prev_group',	$prev_group);
$smarty->assign('offset',		$orioffset);
$smarty->assign('maxage',		$orimaxage);
$smarty->assign('minage',		$oriminage);
$smarty->assign('maxrating',		$orimaxrating);
$smarty->assign('minrating',		$oriminrating);
$smarty->assign('maxcomplete',		$orimaxcomplete);
$smarty->assign('mincomplete',		$orimincomplete);
$smarty->assign('order',		$oriorder);
$smarty->assign('flag',			$oriflag);
$smarty->assign('minsetsize',		$ominsetsize);
$smarty->assign('maxsetsize',		$omaxsetsize);
$smarty->assign('show_advanced_search',	(int) $show_advanced_search);

$smarty->assign('killflag',		$killflag);
$smarty->assign('subscribedgroups',	$subscribedgroups);
$smarty->assign('pages',		$pages);
$smarty->assign('lastpage',		$totalpages);
$smarty->assign('currentpage',		$activepage);
$smarty->assign('allsets',		$allsets);
$smarty->assign('countsets',		count($allsets));
$smarty->assign('__message',		$__message);
$smarty->assign('menu',			generate_menu($LN));
$smarty->assign('maxstrlen',		$prefs['maxsetname']);
$smarty->assign('message',		$message);
$smarty->assign('seteditor',    	is_seteditor($db, $userID));
$smarty->assign('USERSETTYPE_GROUP',   	USERSETTYPE_GROUP);
$smarty->assign('USERSETTYPE_RSS',   	USERSETTYPE_RSS);

$smarty->display('browse.tpl');
?>
